package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.SavingAccountDao;
import csc.fresher.entity.AccountState;
import csc.fresher.entity.AccountTransactionState;
import csc.fresher.entity.CustomerAccount;
import csc.fresher.entity.SavingAccount;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingAccountDaoImpl implements SavingAccountDao {

	@Autowired
	DataSource dataSource;

	private static final Logger logger = Logger
			.getLogger(SavingAccountDaoImpl.class);

	@Override
	public void insert(SavingAccount savingAccount) {
		String query = "INSERT INTO `SavingAccount` (`AccountType`,`SavingAccountBalance`,`isRepeatable`,`CreatedDate`,`AccountState`, `AccountTransactionState`, `CustomerID`) VALUES (?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setByte(1, savingAccount.getAccountType());
			preparedStatement.setDouble(2, savingAccount.getAccountBalance());
			preparedStatement.setBoolean(3, savingAccount.getRepeatable());
			preparedStatement.setTimestamp(4, savingAccount.getCreatedDate());
			preparedStatement.setString(5, savingAccount.getAccountState()
					.getState());
			preparedStatement.setString(6, savingAccount
					.getAccountTransactionState().getState());
			preparedStatement.setLong(7, savingAccount.getCustomerID());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void update(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `SavingAccount`.`AccountType`=?, `SavingAccount`.`AccountBalance`=?, `SavingAccount`.`isRepeatable`=?, `SavingAccount`.`AccountState`=?, `SavingAccount`.`AccountTransactionState`=?, `SavingAccount`.`CustomerID`=? "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setByte(1, savingAccount.getAccountType());
			preparedStatement.setDouble(2, savingAccount.getAccountBalance());
			preparedStatement.setBoolean(3, savingAccount.getRepeatable());
			preparedStatement.setString(4, savingAccount.getAccountState()
					.getState());
			preparedStatement.setString(5, savingAccount
					.getAccountTransactionState().getState());
			preparedStatement.setLong(6, savingAccount.getCustomerID());
			preparedStatement.setLong(7, savingAccount.getAccountNumber());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void delete(SavingAccount savingAccount) {
		// TODO Auto-generated method stub

	}

	/*
	 * @Override public CustomerAccount getCustomerAccountByID(long
	 * cutomerAccountID) { String query =
	 * "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?"
	 * ; SavingAccount savingAccount = null; Connection connection = null;
	 * PreparedStatement preparedStatement = null; ResultSet rs = null; try {
	 * connection = dataSource.getConnection(); preparedStatement =
	 * connection.prepareStatement(query); preparedStatement.setLong(1,
	 * cutomerAccountID); rs = preparedStatement.executeQuery(); if(rs.next())
	 * savingAccount = getSavingAccountResultSet(rs); } catch (SQLException e) {
	 * logger.error(e); e.printStackTrace(); } finally {
	 * DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
	 * logger); } return savingAccount; }
	 */

	@Override
	public CustomerAccount getSavingAccountByCustomerID(long cutomerAccountID) {
		String query = "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cutomerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

	/*
	 * @Override public CustomerAccount getCustomerAccountByCardNumberID(long
	 * cardNumberID) { String query =
	 * "SELECT * FROM `savingaccount` LEFT JOIN `customer`" +
	 * " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`" +
	 * " WHERE `customer`.`IDCardNumber`= ?" +
	 * " ORDER BY `savingaccount`.`CreatedDate` DESC "; SavingAccount
	 * savingAccount = null; Connection connection = null; PreparedStatement
	 * preparedStatement = null; ResultSet rs = null; try { connection =
	 * dataSource.getConnection(); preparedStatement =
	 * connection.prepareStatement(query); preparedStatement.setLong(1,
	 * cardNumberID); rs = preparedStatement.executeQuery(); if(rs.next())
	 * savingAccount = getSavingAccountResultSet(rs); } catch (SQLException e) {
	 * logger.error(e); e.printStackTrace(); } finally {
	 * DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
	 * logger); } return savingAccount; }
	 */

	@Override
	public SavingAccount getCustomerAccountByCardNumberID(long cardNumberID) {
		String query = "SELECT * FROM `savingaccount` LEFT JOIN `customer`"
				+ " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`"
				+ " WHERE `customer`.`IDCardNumber`= ?"
				+ " ORDER BY `savingaccount`.`CreatedDate` DESC ";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cardNumberID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

	private SavingAccount getSavingAccountResultSet(ResultSet rs)
			throws SQLException {
		SavingAccount savingAccount = new SavingAccount();
		savingAccount.setAccountNumber(rs.getLong("SavingAccountNumber"));
		savingAccount.setAccountType(rs.getByte("AccountType"));
		savingAccount.setAccountBalance(rs.getDouble("SavingAccountBalance"));
		savingAccount.setRepeatable(rs.getBoolean("isRepeatable"));
		savingAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
		savingAccount.setAccountState(AccountState.valueOf(rs.getString(
				"AccountState").toUpperCase()));
		savingAccount
				.setAccountTransactionState(AccountTransactionState.valueOf(rs
						.getString("AccountTransactionState").toUpperCase()));
		savingAccount.setCustomerID(rs.getLong("Customer_CustomerID"));
		return savingAccount;
	}

	/*
	 * @Override public List<CustomerAccount>
	 * getListCutomerAccountsbyCardNumberID( long cardNumberID) { // TODO
	 * Auto-generated method stub return null; }
	 */

	@Override
	public List<CustomerAccount> getListCutomerAccountsbyCardNumberID(
			long cardNumberID) {
		List<CustomerAccount> customerAccounts = new ArrayList<CustomerAccount>();
		String query = "SELECT * FROM `savingaccount` LEFT JOIN `customer`"
				+ " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`"
				+ " WHERE `customer`.`IDCardNumber`= ?"
				+ " ORDER BY `savingaccount`.`CreatedDate` DESC";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { cardNumberID });
			if (rs != null) {
				while (rs.next()) {
					savingAccount = getSavingAccountResultSet(rs);
					customerAccounts.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return customerAccounts;
	}

	@Override
	public boolean checkActiveCustomerAccountTransactionState(
			long customerAccountID) {
		String query = "SELECT `AccountTransactionState` FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, customerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next()) {
				if (AccountTransactionState.ACTIVE.getState().equals(
						AccountTransactionState.valueOf(rs
								.getString("AccountTransactionState"))))
					return true;
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return false;
	}

	@Override
	public List<CustomerAccount> getAccountByState(String accountState) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<CustomerAccount> getAccountByIDCardNumber(String idCardNumber) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `IDCardNumber`=?";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			preparedStatement.setString(1, idCardNumber);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccountByFirstName(String firstName) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `FirstName` LIKE '%"
				+ firstName + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("Customer_CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccoungByPhoneNumber(String phoneNumber) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `PhoneNumber` LIKE '%"
				+ phoneNumber + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccountByAddress(String address) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `Address` LIKE '%"
				+ address + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	public static void main(String[] agrs) {
		SavingAccountDao savAccountDao = new SavingAccountDaoImpl();
		List<CustomerAccount> resultList = savAccountDao
				.getAccountByAddress("13");

		for (int i = 0; i < resultList.size(); i++) {
			SavingAccount savAccount = (SavingAccount) resultList.get(i);
			System.out.println(savAccount.getCustomerID());
		}
	}

	@Override
	public CustomerAccount getCustomerAccountByID(long cutomerAccountID) {
		String query = "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cutomerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

}
